import xlrd
import pymysql

# ########################把excel数据导入MySQL数据库 start ######################################
# 1) 通过xlrd模块读取Excel数据
# 2) 通过pymysql模块连接数据库
# 3) 组装数据／执行插入操作
# 4) 关闭数据库连接
excelData = xlrd.open_workbook("../myFile/data2.xlsx")
sheet = excelData.sheet_by_index(0)
questionList = []  # 构建试题列表


class Question:  # 试题类
    pass

# 循环sheet行
for i in range(sheet.nrows):
    if i > 1:
        obj = Question()  # 构建试题对象
        obj.subject = sheet.cell(i, 1).value  # 题目
        obj.questionType = sheet.cell(i, 2).value  # 题型
        obj.optionA = sheet.cell(i, 3).value  # 选项A
        obj.optionB = sheet.cell(i, 4).value  # 选项B
        obj.optionC = sheet.cell(i, 5).value  # 选项C
        obj.optionD = sheet.cell(i, 6).value  # 选项D
        obj.score = sheet.cell(i, 7).value  # 分值
        obj.answer = sheet.cell(i, 8).value  # 正确答案
        questionList.append(obj)
print(len(questionList))
print("---------------------------------------------------------")
# 连接MySQL数据库
conn = pymysql.connect(host='localhost', port=3306, user='root', password='leon-root', charset='utf8', database='test')
cursor = conn.cursor()  # 获得Cursor对象

val = []  # 空列表 存储Excel表格的数据
for item in questionList:
    val.append((item.subject, item.questionType, item.optionA, item.optionB, item.optionC, item.optionD, item.score,item.answer))
for v in val:  # 循环插入数据到DB
    sqlStr = "insert into question values(null,'{}','{}','{}','{}','{}','{}','{}','{}');"
    sql = sqlStr.format(v[0], v[1], v[2], v[3], v[4], v[5], v[6], v[7])
    print(sql)
    cursor.execute(sql)  # 执行SQL
conn.commit()  # 提交数据
# ########################把excel数据导入MySQL数据库 end ######################################

